<?php
// +----------------------------------------------------------------------
// | 数据导出导入
// composer require phpoffice/phpspreadsheet
// exportExcel 导出
// importExcel 导入
// +----------------------------------------------------------------------
namespace App\Utils;

use PhpOffice\PhpSpreadsheet\Reader\Xlsx;
use PhpOffice\PhpSpreadsheet\Reader\Xls;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Worksheet\PageSetup;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

class PhpSpreadsheetUtil
{
    //列
    const cellName = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ'];

    /**
     * Excel导出
     * @param array  $datas      导出数据，二维数组[[111,222,333],[444,555,666]]
     * @param array  $heads      导出表头数据，一维数组['序号','名称','时间']
     * @param string $fileName   导出文件名称
     * @param array  $options    操作选项，例如：
     *                           bool   print       设置打印格式
     *                           string freezePane  锁定行数，例如表头为第一行，则锁定表头输入A2
     *                           array  setARGB     设置背景色，例如['A1', 'C1']
     *                           array  setWidth    设置宽度，例如['A' => 30, 'C' => 20]
     *                           bool   setBorder   设置单元格边框
     *                           array  mergeCells  设置合并单元格，例如['A1:J1' => 'A1:J1']
     *                           array  formula     设置公式，例如['F2' => '=IF(D2>0,E42/D2,0)']
     *                           array  format      设置格式，整列设置，例如['A' => 'General']
     *                           array  alignCenter 设置居中样式，例如['A1', 'A2']
     *                           array  bold        设置加粗样式，例如['A1', 'A2']
     *                           array  setAlignment 设置对齐样式，例如['A' => 'General']
     *                           string savePath    保存目录，设置后则文件保存到服务器，不通过浏览器下载
     */
    public function exportExcel(array $datas, array $heads, string $fileName = '', array $options = []): bool
    {
        try {
            if (empty($datas)) {
                return false;
            }
            if (empty($heads)) {
                return false;
            }

            set_time_limit(0);
            /** @var Spreadsheet $objSpreadsheet */
            $objSpreadsheet = app(Spreadsheet::class);

            $writer = new \PhpOffice\PhpSpreadsheet\Writer\Html($objSpreadsheet);
            $writer->setPreCalculateFormulas(false);

            /* 设置默认文字居左，上下居中 */
            $styleArray = [
                'alignment' => [
                    'horizontal' => Alignment::HORIZONTAL_LEFT,
                    'vertical'   => Alignment::VERTICAL_CENTER,
                ],
            ];
            $objSpreadsheet->getDefaultStyle()->applyFromArray($styleArray);
            /* 设置Excel Sheet */
            $activeSheet = $objSpreadsheet->setActiveSheetIndex(0);

            /* 打印设置 */
            if (isset($options['print']) && $options['print']) {
                /* 设置打印为A4效果 */
                $activeSheet->getPageSetup()->setPaperSize(PageSetup:: PAPERSIZE_A4);
                /* 设置打印时边距 */
                $pValue = 1 / 2.54;
                $activeSheet->getPageMargins()->setTop($pValue / 2);
                $activeSheet->getPageMargins()->setBottom($pValue * 2);
                $activeSheet->getPageMargins()->setLeft($pValue / 2);
                $activeSheet->getPageMargins()->setRight($pValue / 2);
            }

            //对应列数
            $cellName = self::cellName;

            //处理导出格式
            $head[] = $heads;
            $datas = array_merge($head,$datas);
            foreach ($datas as $k => $v) {
                $arr = [];
                $i = 0;
                foreach ($v as $k1 => $v1) {
                    $arr[$cellName[$i].($k+1)] = $v1;
                    $i++;
                }
                $news_arr[] = $arr;
            }

            /* 行数据处理 */
            foreach ($news_arr as $k => $v) {
                foreach ($v as $sKey => $sItem) {
                    /* 默认文本格式 */
                    $pDataType = DataType::TYPE_STRING;

                    /* 设置单元格格式 */
                    if (isset($options['format']) && !empty($options['format'])) {
                        $colRow = Coordinate::coordinateFromString($sKey);

                        /* 存在该列格式并且有特殊格式 */
                        if (isset($options['format'][$colRow[0]]) &&
                            NumberFormat::FORMAT_GENERAL != $options['format'][$colRow[0]]) {
                            $activeSheet->getStyle($sKey)->getNumberFormat()
                                ->setFormatCode($options['format'][$colRow[0]]);

                            if (false !== strpos($options['format'][$colRow[0]], '0.00') &&
                                is_numeric(str_replace(['￥', ','], '', $sItem))) {
                                /* 数字格式转换为数字单元格 */
                                $pDataType = DataType::TYPE_NUMERIC;
                                $sItem     = str_replace(['￥', ','], '', $sItem);
                            }
                        } elseif (is_int($sItem)) {
                            $pDataType = DataType::TYPE_NUMERIC;
                        }
                    }

                    $activeSheet->setCellValueExplicit($sKey, $sItem, $pDataType);

                    /* 存在:形式的合并行列，列入A1:B2，则对应合并 */
                    if (false !== strstr($sKey, ":")) {
                        $options['mergeCells'][$sKey] = $sKey;
                    }
                }
            }

            unset($datas);

            /* 设置锁定行 */
            if (isset($options['freezePane']) && !empty($options['freezePane'])) {
                $activeSheet->freezePane($options['freezePane']);
                unset($options['freezePane']);
            }

            /* 设置宽度 */
            if (isset($options['setWidth']) && !empty($options['setWidth'])) {
                foreach ($options['setWidth'] as $swKey => $swItem) {
                    $activeSheet->getColumnDimension($swKey)->setWidth($swItem);
                }

                unset($options['setWidth']);
            }

            /* 设置背景色 */
            if (isset($options['setARGB']) && !empty($options['setARGB'])) {
                foreach ($options['setARGB'] as $sItem) {
                    $activeSheet->getStyle($sItem)
                        ->getFill()->setFillType(Fill::FILL_SOLID)
                        ->getStartColor()->setARGB(Color::COLOR_YELLOW);
                }

                unset($options['setARGB']);
            }

            /* 设置公式 */
            if (isset($options['formula']) && !empty($options['formula'])) {
                foreach ($options['formula'] as $fKey => $fItem) {
                    $activeSheet->setCellValue($fKey, $fItem);
                }

                unset($options['formula']);
            }

            /* 合并行列处理 */
            if (isset($options['mergeCells']) && !empty($options['mergeCells'])) {
                $activeSheet->setMergeCells($options['mergeCells']);
                unset($options['mergeCells']);
            }

            /* 设置居中 */
            if (isset($options['alignCenter']) && !empty($options['alignCenter'])) {
                $styleArray = [
                    'alignment' => [
                        'horizontal' => Alignment::HORIZONTAL_CENTER,
                        'vertical'   => Alignment::VERTICAL_CENTER,
                    ],
                ];

                foreach ($options['alignCenter'] as $acItem) {
                    $activeSheet->getStyle($acItem)->applyFromArray($styleArray);
                }

                unset($options['alignCenter']);
            }

            /* 设置加粗 */
            if (isset($options['bold']) && !empty($options['bold'])) {
                foreach ($options['bold'] as $bItem) {
                    $activeSheet->getStyle($bItem)->getFont()->setBold(true);
                }

                unset($options['bold']);
            }

            /* 设置单元格对齐 */
            if (isset($options['setAlignment']) && !empty($options['setAlignment'])) {
                foreach ($options['setAlignment'] as $k => $v) {
                    $styleArray = [
                        'alignment' => self::setAlignments($v),
                    ];
                    $activeSheet->getStyle($k)->applyFromArray($styleArray);
                }
                unset($options['setAlignment']);
            }

            /* 设置单元格边框，整个表格设置即可，必须在数据填充后才可以获取到最大行列 */
            if (isset($options['setBorder']) && $options['setBorder']) {
                $border    = [
                    'borders' => [
                        'allBorders' => [
                            'borderStyle' => Border::BORDER_THIN, // 设置border样式
                            'color'       => ['argb' => 'FF000000'], // 设置border颜色
                        ],
                    ],
                ];
                $setBorder = 'A1:' . $activeSheet->getHighestColumn() . $activeSheet->getHighestRow();
                $activeSheet->getStyle($setBorder)->applyFromArray($border);
                unset($options['setBorder']);
            }

            $fileName = !empty($fileName) ? $fileName : (date('YmdHis') . '.xlsx');

            if (!isset($options['savePath'])) {
                /* 直接导出Excel，无需保存到本地，输出07Excel文件 */
                header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
                header(
                    "Content-Disposition:attachment;filename=" . iconv(
                        "utf-8", "GB2312//TRANSLIT", $fileName
                    )
                );
                header('Cache-Control: max-age=0');//禁止缓存
                $savePath = 'php://output';
            } else {
                // 判断保存的目录是否存在
                $savePath = public_path('uploads/'.$options['savePath']);
                if(!file_exists($savePath)){
                    mkdir($savePath,0777,TRUE);
                    chmod($savePath,0777);
                }
                $savePath = $savePath."/".$fileName;
                $FilePath = $_SERVER['HTTP_ORIGIN']."/uploads/".$options['savePath']."/".$fileName;
                cache(['ExcelFilePath'=>$FilePath],120);
            }

            ob_clean();
            ob_start();
            $objWriter = IOFactory::createWriter($objSpreadsheet, 'Xlsx');
            $objWriter->save($savePath);

            /* 释放内存 */
            $objSpreadsheet->disconnectWorksheets();
            unset($objSpreadsheet);
            ob_end_flush();

            return true;
        } catch (\Exception $e) {
            return false;
        }
    }

    /**
     * 设置单元格对齐方式
     * @param string $value [description]
     */
    public static function setAlignments(string $v = "")
    {
        switch (strtolower($v)) {
            case 'center':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_CENTER];
                break;
            case 'top':
                $horizontal = ['horizontal' => Alignment::VERTICAL_TOP];
                break;
            case 'right':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_RIGHT];
                break;
            case 'bottom':
                $horizontal = ['horizontal' => Alignment::VERTICAL_BOTTOM];
                break;
            case 'diagonal':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_DIAGONAL];
                break;
            case 'centercontinuous':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_CENTER_CONTINUOUS];
                break;
            case 'justify':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_JUSTIFY];
                break;
            case 'fill':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_FILL];
                break;
            case 'distributed':
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_DISTRIBUTED];
                break;
            default:
                $horizontal = ['horizontal' => Alignment::HORIZONTAL_LEFT];
                break;
        }
        return $horizontal;
    }

    /**
     * 导入数据
     * @param [type] $file_url  文件绝对路径
     * @param array $field 英文字段名称，可以对应数据库字段名称或自定义字段名称
     * @param integer $ini_row 起始行，即从第几行开始获取数据
     * @return array $list 返回文件中所有数据
     */
    public function importExcel($file_url, $field = [], $ini_row = 2)
    {
        if(empty($file_url)) return ['code'=>500, 'msg'=>'请选择导入的文件'];
        if(empty($field)) return ['code'=>500, 'msg'=>'请定义英文字段名称'];
        $objPHPExcel = IOFactory::load($file_url);
        $sheet = $objPHPExcel->getActiveSheet();

        $cellName = self::cellName;
        $cellName_arr = array_flip($cellName);
        $cellCount = $cellName_arr[$sheet->getHighestColumn()] ? ($cellName_arr[$sheet->getHighestColumn()] + 1) : 0;  //总列数
        if($cellCount != count($field)) return ['code'=>500, 'msg'=>'定义字段与文件列总数不相符，请一一对应检查'];

        $list = [];
        foreach ($sheet->getRowIterator($ini_row) as $row) {
            $tmp = [];
            $i = 0;
            foreach ($row->getCellIterator() as $cell) {
                $tmp[$field[$i]] = $cell->getFormattedValue();
                $i++;
            }
            $list[] = $tmp;
        }
        return $list;
    }
}
